{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nan = pd.read_excel('./18级高一体测成绩汇总.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nv = pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('./体侧成绩评分表.xls',header = [0,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    男肺活量       女肺活量      男50米跑      女50米跑       男体前屈       ...  女跳远       \\\n",
      "      成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数  ...   成绩   分数   \n",
      "0   4540  100  3150  100   7.1  100   7.8  100  23.6  100  ...  204  100   \n",
      "1   4420   95  3100   95   7.2   95   7.9   95  21.5   95  ...  198   95   \n",
      "2   4300   90  3050   90   7.3   90   8.0   90  19.4   90  ...  192   90   \n",
      "3   4050   85  2900   85   7.4   85   8.3   85  17.2   85  ...  185   85   \n",
      "4   3800   80  2750   80   7.5   80   8.6   80  15.0   80  ...  178   80   \n",
      "5   3680   78  2650   78   7.7   78   8.8   78  13.6   78  ...  175   78   \n",
      "6   3560   76  2550   76   7.9   76   9.0   76  12.2   76  ...  172   76   \n",
      "7   3440   74  2450   74   8.1   74   9.2   74  10.8   74  ...  169   74   \n",
      "8   3320   72  2350   72   8.3   72   9.4   72   9.4   72  ...  166   72   \n",
      "9   3200   70  2250   70   8.5   70   9.6   70   8.0   70  ...  163   70   \n",
      "10  3080   68  2150   68   8.7   68   9.8   68   6.6   68  ...  160   68   \n",
      "11  2960   66  2050   66   8.9   66  10.0   66   5.2   66  ...  157   66   \n",
      "12  2840   64  1950   64   9.1   64  10.2   64   3.8   64  ...  154   64   \n",
      "13  2720   62  1850   62   9.3   62  10.4   62   2.4   62  ...  151   62   \n",
      "14  2600   60  1750   60   9.5   60  10.6   60   1.0   60  ...  148   60   \n",
      "15  2470   50  1710   50   9.7   50  10.8   50   0.0   50  ...  143   50   \n",
      "16  2340   40  1670   40   9.9   40  11.0   40  -1.0   40  ...  138   40   \n",
      "17  2210   30  1630   30  10.1   30  11.2   30  -2.0   30  ...  133   30   \n",
      "18  2080   20  1590   20  10.3   20  11.4   20  -3.0   20  ...  128   20   \n",
      "19  1950   10  1550   10  10.5   10  11.6   10  -4.0   10  ...  123   10   \n",
      "\n",
      "     男引体      女仰卧      男1000米跑      女800米跑       \n",
      "      成绩   分数  成绩   分数      成绩   分数     成绩   分数  \n",
      "0   16.0  100  53  100   3'30\"  100  3'24\"  100  \n",
      "1   15.0   95  51   95   3'35\"   95  3'30\"   95  \n",
      "2   14.0   90  49   90   3'40\"   90  3'36\"   90  \n",
      "3   13.0   85  46   85   3'47\"   85  3'43\"   85  \n",
      "4   12.0   80  43   80   3'55\"   80  3'50\"   80  \n",
      "5    NaN   78  41   78   4'00\"   78  3'55\"   78  \n",
      "6   11.0   76  39   76   4'05\"   76  4'00\"   76  \n",
      "7    NaN   74  37   74   4'10\"   74  4'05\"   74  \n",
      "8   10.0   72  35   72   4'15\"   72  4'10\"   72  \n",
      "9    NaN   70  33   70   4'20\"   70  4'15\"   70  \n",
      "10   9.0   68  31   68   4'25\"   68  4'20\"   68  \n",
      "11   NaN   66  29   66   4'30\"   66  4'25\"   66  \n",
      "12   8.0   64  27   64   4'35\"   64  4'30\"   64  \n",
      "13   NaN   62  25   62   4'40\"   62  4'35\"   62  \n",
      "14   7.0   60  23   60   4'45\"   60  4'40\"   60  \n",
      "15   6.0   50  21   50   5'05\"   50  4'50\"   50  \n",
      "16   5.0   40  19   40   5'25\"   40  5'00\"   40  \n",
      "17   4.0   30  17   30   5'45\"   30  5'10\"   30  \n",
      "18   3.0   20  15   20   6'05\"   20  5'20\"   20  \n",
      "19   2.0   10  13   10   6'25\"   10  5'30\"   10  \n",
      "\n",
      "[20 rows x 24 columns]\n"
     ]
    }
   ],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0   1  男     4.13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1   1  男     4.16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2   1  男     4.09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3   1  男     4.21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4   1  男     3.44   7.52  210.0    13    9  3538  171.0  54.7    0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def con2(x):\n",
    "    if isinstance(x,str):\n",
    "        m,s = x.split(\"'\")\n",
    "        m,s = int(m),int(s)\n",
    "        return m + s/100.0\n",
    "    else:\n",
    "        return x\n",
    "df_nan['男1000米跑'] = df_nan['男1000米跑'].map(con2)\n",
    "df_nan.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of      班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高         体重  BMI\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.599998  0.0\n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.700001  0.0\n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.500000  0.0\n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.699997  0.0\n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.700001  0.0\n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...        ...  ...\n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.500000  0.0\n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.000000  0.0\n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.000000  0.0\n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.700001  0.0\n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.000000  0.0\n",
       "\n",
       "[477 rows x 11 columns]>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nan.loc[:,'男1000米跑':] = df_nan.loc[:,'男1000米跑':].astype('float32',copy = False)\n",
    "df_nv.loc[:,'女800米跑':] = df_nv.loc[:,'女800米跑':].astype('float32',copy = False)\n",
    "df_nan.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "班级           int64\n",
       "性别          object\n",
       "男1000米跑    float32\n",
       "男50米跑      float32\n",
       "男跳远        float32\n",
       "男体前屈       float32\n",
       "男引体        float32\n",
       "男肺活量       float32\n",
       "身高         float32\n",
       "体重         float32\n",
       "BMI        float32\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nan.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#清洗数据\n",
    "def con1(x):\n",
    "    m,s = [int(i) for i in x.strip('\"').split(\"'\")]\n",
    "    return m + s/100.0\n",
    "# 男女生数据类型转换\n",
    "df.iloc[:,-4] = df.iloc[:,-4].transform(con1)\n",
    "\n",
    "df.iloc[:,-2] = df.iloc[:,-2].transform(con1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "男肺活量     成绩      int64\n",
       "         分数      int64\n",
       "女肺活量     成绩      int64\n",
       "         分数      int64\n",
       "男50米跑    成绩    float64\n",
       "         分数      int64\n",
       "女50米跑    成绩    float64\n",
       "         分数      int64\n",
       "男体前屈     成绩    float64\n",
       "         分数      int64\n",
       "女体前屈     成绩    float64\n",
       "         分数      int64\n",
       "男跳远      成绩      int64\n",
       "         分数      int64\n",
       "女跳远      成绩      int64\n",
       "         分数      int64\n",
       "男引体      成绩    float64\n",
       "         分数      int64\n",
       "女仰卧      成绩      int64\n",
       "         分数      int64\n",
       "男1000米跑  成绩    float64\n",
       "         分数      int64\n",
       "女800米跑   成绩    float64\n",
       "         分数      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "run = ['男1000米跑','男50米跑']\n",
    "for r in run:\n",
    "    def con(x):\n",
    "        if x == 0: \n",
    "            return 0\n",
    "        for i in range(df[r].shape[0]):\n",
    "            if x <= df[r]['成绩'][i]:\n",
    "                return df[r]['分数'][i]\n",
    "        return 0 \n",
    "    df_nan[r + '分数'] = df_nan[r].map(con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of      班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高         体重  BMI  \\\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.599998  0.0   \n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.700001  0.0   \n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.500000  0.0   \n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.699997  0.0   \n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.700001  0.0   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...        ...  ...   \n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.500000  0.0   \n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.000000  0.0   \n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.000000  0.0   \n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.700001  0.0   \n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.000000  0.0   \n",
       "\n",
       "     男1000米跑分数  男50米跑分数  \n",
       "0           72       66  \n",
       "1           70       78  \n",
       "2           74       70  \n",
       "3           68       74  \n",
       "4           85       78  \n",
       "..         ...      ...  \n",
       "472         68       72  \n",
       "473         40       50  \n",
       "474        100       80  \n",
       "475         62       76  \n",
       "476          0        0  \n",
       "\n",
       "[477 rows x 13 columns]>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nan.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.699997</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "      <td>66</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高         体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.599998  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.700001  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.500000  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.699997  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.700001  0.0   \n",
       "\n",
       "   男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  \n",
       "0         72       66     60      74  \n",
       "1         70       78     74      74  \n",
       "2         74       70     70      78  \n",
       "3         68       74     64      76  \n",
       "4         85       78     66      76  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jump = ['男跳远', '男体前屈']\n",
    "\n",
    "for j in jump:\n",
    "    def con(x):\n",
    "        if x == 0: \n",
    "            return 0\n",
    "        for i in range(df[j].shape[0]):\n",
    "            if x >= df[j]['成绩'][i]:\n",
    "                return df[j]['分数'][i]\n",
    "        return 0 \n",
    "    df_nan[j + '分数'] = df_nan[j].apply(con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of      班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高         体重  BMI  \\\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.599998  0.0   \n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.700001  0.0   \n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.500000  0.0   \n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.699997  0.0   \n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.700001  0.0   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...        ...  ...   \n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.500000  0.0   \n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.000000  0.0   \n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.000000  0.0   \n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.700001  0.0   \n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.000000  0.0   \n",
       "\n",
       "     男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  \n",
       "0           72       66     60      74  \n",
       "1           70       78     74      74  \n",
       "2           74       70     70      78  \n",
       "3           68       74     64      76  \n",
       "4           85       78     66      76  \n",
       "..         ...      ...    ...     ...  \n",
       "472         68       72     66      72  \n",
       "473         40       50     66      80  \n",
       "474        100       80     90      76  \n",
       "475         62       76     66      78  \n",
       "476          0        0      0       0  \n",
       "\n",
       "[477 rows x 15 columns]>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nan.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#列索引重排\n",
    "cp = ['班级', '性别', '男1000米跑','男1000米跑分数',  '男50米跑','男50米跑分数', \n",
    "        '男跳远','男跳远分数', '男体前屈','男体前屈分数', '男引体', '男肺活量', '身高',\n",
    "       '体重', 'BMI']\n",
    "df_nan = df_nan[cp]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of      班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  \\\n",
       "0     1  男     4.13         72   8.88       66  195.0     60  12.0      74   \n",
       "1     1  男     4.16         70   7.70       78  225.0     74  11.0      74   \n",
       "2     1  男     4.09         74   8.45       70  218.0     70  14.0      78   \n",
       "3     1  男     4.21         68   8.05       74  206.0     64  13.0      76   \n",
       "4     1  男     3.44         85   7.52       78  210.0     66  13.0      76   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23         68   8.27       72  208.0     66  10.0      72   \n",
       "473  17  男     5.19         40   9.55       50  210.0     66  15.0      80   \n",
       "474  17  男     3.25        100   7.50       80  252.0     90  13.0      76   \n",
       "475  17  男     4.39         62   7.81       76  208.0     66  14.0      78   \n",
       "476  17  男     0.00          0   0.00        0    0.0      0   0.0       0   \n",
       "\n",
       "      男引体    男肺活量     身高         体重  BMI  \n",
       "0     1.0  2785.0  170.0  72.599998  0.0  \n",
       "1     7.0  3133.0  174.0  52.700001  0.0  \n",
       "2     1.0  3901.0  169.0  46.500000  0.0  \n",
       "3     1.0  4946.0  183.0  79.699997  0.0  \n",
       "4     9.0  3538.0  171.0  54.700001  0.0  \n",
       "..    ...     ...    ...        ...  ...  \n",
       "472   0.0  4647.0  176.0  69.500000  0.0  \n",
       "473   6.0  7042.0  177.0  76.000000  0.0  \n",
       "474  13.0  5755.0  181.0  65.000000  0.0  \n",
       "475  11.0  5688.0  172.0  51.700001  0.0  \n",
       "476   0.0     0.0    0.0   0.000000  0.0  \n",
       "\n",
       "[477 rows x 15 columns]>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nan.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#女"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高         体重  BMI  \\\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.299999  0.0   \n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.599998  0.0   \n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.000000  0.0   \n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.700001  0.0   \n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.900002  0.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  \n",
       "0       100       72  \n",
       "1        40       10  \n",
       "2        80        0  \n",
       "3        85       70  \n",
       "4        80       68  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run = ['女800米跑','女50米跑']\n",
    "\n",
    "for r in run:\n",
    "    def con(x):\n",
    "        if x == 0: \n",
    "            return 0\n",
    "        for i in range(df[r].shape[0]):\n",
    "            if x <= df[r]['成绩'][i]:\n",
    "                return df[r]['分数'][i]\n",
    "        return 0 \n",
    "    df_nv[r + '分数'] = df_nv[r].transform(con)\n",
    "df_nv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>66</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "      <td>76</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>50</td>\n",
       "      <td>64</td>\n",
       "      <td>70</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高         体重  BMI  \\\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.299999  0.0   \n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.599998  0.0   \n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.000000  0.0   \n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.700001  0.0   \n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.900002  0.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  女跳远分数  女体前屈分数  女仰卧分数  女肺活量分数  \n",
       "0       100       72     85      76     85     100  \n",
       "1        40       10     60      66     66     100  \n",
       "2        80        0     60      64     76     100  \n",
       "3        85       70     76      90     85     100  \n",
       "4        80       68     50      64     70     100  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jump = ['女跳远', '女体前屈']\n",
    "for j in jump:\n",
    "    def con(x):\n",
    "        if x == 0: \n",
    "            return 0\n",
    "        for i in range(df[j].shape[0]):\n",
    "            if x >= df[j]['成绩'][i]:\n",
    "                return df[j]['分数'][i]\n",
    "        return 0 \n",
    "    df_nv[j + '分数'] = df_nv[j].apply(con)\n",
    "df_nv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#列索引重排\n",
    "cp = ['班级', '性别', '女800米跑','女800米跑分数',  '女50米跑','女50米跑分数', \n",
    "        '女跳远','女跳远分数', '女体前屈','女体前屈分数', '女仰卧', \n",
    "        '女肺活量', '身高','体重', 'BMI']\n",
    "df_nv = df_nv[cp]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of      班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数  \\\n",
       "0     1  女    3.22       100   9.32       72  185.0     85  16.0      76   \n",
       "1     1  女    4.59        40  11.44       10  148.0     60   9.0      66   \n",
       "2     1  女    3.46        80  13.40        0  150.0     60   7.0      64   \n",
       "3     1  女    3.39        85   9.52       70  172.0     76  21.0      90   \n",
       "4     1  女    3.43        80   9.79       68  145.0     50   8.0      64   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...     ...   \n",
       "588  17  女    3.51        78   9.60       68  150.0     60  24.0      95   \n",
       "589  17  女    4.00        76  10.18       64  150.0     60  13.0      72   \n",
       "590  17  女    3.45        80  10.18       64  152.0     62  15.0      76   \n",
       "591  17  女    4.01        74   9.67       68  165.0     70  10.0      68   \n",
       "592  17  女    4.48        50   9.09       74  180.0     80  10.0      68   \n",
       "\n",
       "      女仰卧    女肺活量     身高         体重  BMI  \n",
       "0    48.0  3775.0  163.0  51.299999  0.0  \n",
       "1    29.0  3683.0  163.0  66.599998  0.0  \n",
       "2    40.0  3331.0  157.0  60.000000  0.0  \n",
       "3    46.0  3701.0  160.0  50.700001  0.0  \n",
       "4    34.0  3592.0  167.0  63.900002  0.0  \n",
       "..    ...     ...    ...        ...  ...  \n",
       "588  41.0  2255.0  158.0  49.000000  0.0  \n",
       "589  36.0  2937.0  161.0  55.700001  0.0  \n",
       "590  35.0  2592.0  165.0  48.599998  0.0  \n",
       "591  41.0  1829.0  154.0  43.599998  0.0  \n",
       "592  46.0  2962.0  162.0  55.299999  0.0  \n",
       "\n",
       "[593 rows x 15 columns]>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nv.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
